﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using mdryden.Core.Security;
using System.Diagnostics;
using LastoRevival.Service.Contracts.Models;
using LastoRevival.Service.Implementations.Data.Sql.Converters;
using mdryden.Core.Data;
using mdryden.Core.Data.Sql;
using LastoRevival.Service.Implementations.Settings;
using LastoRevival.Service.Implementations.Models;

namespace LastoRevival.Service.Implementations.Data.Sql
{
	class SqlDataProvider : IDataProvider
	{
		private string connectionString;

		private PreviewForumModelCreator previewForumModelCreator;
		private ThreadsCreator threadsCreator;
		private ViewPostModelCreator viewPostModelCreator;
		private UserProfileDetailsCreator userProfileCreator;
		private EditPostModelCreator editPostModelCreator;
		private ForumAccessConfigurationCreator forumAccessConfigCreator;
		private UserStatsCreator userStatsCreator;
		private EditUserModelCreator editUserCreator;


		public static SqlDataProvider Create(string connectionString)
		{
			var previewForumModelCreator = new PreviewForumModelCreator();
			var threadCreator = new ThreadCreator();
			var viewPostModelCreator = new ViewPostModelCreator(threadCreator);
			var forumPageConverter = new ThreadsCreator(threadCreator);
			var userProfileCreator = new UserProfileDetailsCreator();
			var editPostModelCreator = new EditPostModelCreator();
			var forumAccessConfigCreator = new ForumAccessConfigurationCreator();
			var userStatsCreator = new UserStatsCreator();
			var editUserCreator = new EditUserModelCreator();

			var instance = new SqlDataProvider
			{
				connectionString = connectionString,
				previewForumModelCreator = previewForumModelCreator,
				viewPostModelCreator = viewPostModelCreator,
				threadsCreator = forumPageConverter,
				userProfileCreator = userProfileCreator,
				editPostModelCreator = editPostModelCreator,
				forumAccessConfigCreator = forumAccessConfigCreator,
				userStatsCreator = userStatsCreator,
				editUserCreator = editUserCreator,
			};

			return instance;
		}

		#region Helper Methods

		private void ValidateRowCount(int expectedRowCount, DataTable table)
		{
			if (table.Rows.Count != expectedRowCount)
			{
				throw new InvalidRowCountException(expectedRowCount, table.Rows.Count);
			}
		}

		#endregion

		public IEnumerable<PreviewForumModel> GetVisibleForums(int groupID)
		{
			var command = new GetVisibleForumsCommand(groupID);
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				table = manager.GetDataTable(command);
			}

			return previewForumModelCreator.Create(table.Rows.OfType<DataRow>());
		}

		public IEnumerable<PreviewForumModel> GetAllForums()
		{
			var command = new GetAllForumsCommand();
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				table = manager.GetDataTable(command);
			}

			return previewForumModelCreator.Create(table.Rows.OfType<DataRow>());
		}

		public Threads GetThreadsOnPage(int forumID, int currentPage, int pageSize)
		{
			var forum = GetForum(forumID);

			var getMessagesCommand = new GetMessagesOnForumPageCommand(forumID, currentPage, pageSize);

			DataTable messageTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				messageTable = manager.GetDataTable(getMessagesCommand);
			}

			return threadsCreator.Create(messageTable.Rows.OfType<DataRow>(), currentPage, pageSize, forumID, forum.ThreadCount);

		}

		public string GetPasswordHash(string username)
		{
			var command = new GetUserByNameCommand(username);
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				table = manager.GetDataTable(command);
			}

			if (table.Rows.Count != 1)
			{
				throw new InvalidRowCountException(1, table.Rows.Count);
			}

			var dbPassword = string.Format("{0}", table.Rows[0]["userpassword"]);
			return dbPassword;

		}

		public VerificationCode GetUserVerificationCode(string username)
		{
			var command = new GetVerificationStateCommand(username);
			DataTable result;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				result = manager.GetDataTable(command);
			}

			if (result.Rows.Count != 1)
			{
				throw new InvalidRowCountException(1, result.Rows.Count);
			}

			var code = (string)result.Rows[0]["verifyCode"];
			var expiration = (DateTime)result.Rows[0]["verifyExpire"];

			return new VerificationCode(code, expiration);
		}

		public PreviewForumModel GetForum(int forumID)
		{
			var command = new GetForumByIDCommand(forumID);
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				table = manager.GetDataTable(command);
			}

			ValidateRowCount(1, table);

			return previewForumModelCreator.Create(table.Rows[0]);
		}


		public int CreatePost(int userID, CreatePostModel post)
		{
			var command = new InsertPostCommand(userID, post);
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, true))
			{
				try
				{
					table = manager.GetDataTable(command);
					manager.Commit();
				}
				catch
				{
					manager.Rollback();
					throw;
				}
			}

			ValidateRowCount(1, table);

			return int.Parse(string.Format("{0}", table.Rows[0]["id"]));

		}


		public ViewPostModel GetPostForViewing(int postID)
		{
			// always update on viewing
			var activePostCommand = new GetPostCommand(postID, true);

			DataTable activePostTable;
			DataTable messagesTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				activePostTable = manager.GetDataTable(activePostCommand);
				// make sure we found the post
				ValidateRowCount(1, activePostTable);

				// get the threadID from the post table
				var threadID = (int)activePostTable.Rows[0]["thread_id"];
				if (threadID == 0) threadID = postID;

				var getMessagesCommand = new GetMessagesInThreadCommand(threadID);

				messagesTable = manager.GetDataTable(getMessagesCommand);
			}

			var model = viewPostModelCreator.Create(activePostTable.Rows[0], messagesTable.Rows);

			return model;
		}

		public EditPostModel GetPostForEditing(int postID)
		{
			var getPostCommand = new GetPostCommand(postID, false); // never update view counts for edit

			DataTable postTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				postTable = manager.GetDataTable(getPostCommand);
			}

			ValidateRowCount(1, postTable);

			var model = editPostModelCreator.Create(postTable.Rows[0]);

			return model;
		}

		private UserProfileDetails GetProfile(DataTable userTable)
		{
			// we could have 0 or 1 results here
			if (userTable.Rows.Count == 0)
			{
				throw new InvalidRowCountException(1, 0);
			}

			// make sure it's not 2 or something insane
			ValidateRowCount(1, userTable);

			return userProfileCreator.Create(userTable.Rows[0]);
		}

		public UserProfileDetails GetProfile(string username)
		{
			var command = new GetUserByNameCommand(username);

			DataTable userTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				userTable = manager.GetDataTable(command);
			}

			return GetProfile(userTable);
		}

		public UserProfileDetails GetProfile(Guid loginToken)
		{
			var command = new GetUserByLoginToken(loginToken);
			DataTable userTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				userTable = manager.GetDataTable(command);
			}

			return GetProfile(userTable);
		}

		public UserStats GetUserStats(int userID)
		{
			var command = new GetUserStatsCommand(userID);

			DataTable statsTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				statsTable = manager.GetDataTable(command);
			}

			ValidateRowCount(1, statsTable);

			return userStatsCreator.Create(statsTable.Rows[0]);

		}

		public ForumAccessConfiguration GetForumAccessForGroup(int groupID)
		{
			var forumsCommand = new GetForumAccessForGroupCommand(groupID);
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				table = manager.GetDataTable(forumsCommand);
			}

			return forumAccessConfigCreator.Create(table.Rows.OfType<DataRow>());

		}

		public bool IsUsernameAvailable(string username)
		{
			// I made this a seperate call instead of a part of CreateUser to allow for an ajax call at some point
			// if desired.

			// verify that the username is available
			var getUserCommand = new GetUserByNameCommand(username);

			DataTable userRows;
			using (var manager = SqlManager.Create(connectionString, false))
			{
				userRows = manager.GetDataTable(getUserCommand);
			}

			// no rows, username is available
			return userRows.Rows.Count == 0;
		}

		public int CreateUser(CreateUserModel user, VerificationCode verificationCode, int groupID)
		{
			var command = new InsertUserCommand(user, verificationCode.Code, verificationCode.Expiration, groupID);
			DataTable userTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				userTable = manager.GetDataTable(command);
			}

			// will eventually use this ID to fire off the verification email
			var userID = int.Parse(string.Format("{0}", userTable.Rows[0]["id"]));
			return userID;

		}

		public Guid StartUserSession(string username, DateTime expirationDate)
		{
			var command = new InsertSessionCommand(username, expirationDate);
			DataTable result;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				result = manager.GetDataTable(command);
			}

			ValidateRowCount(1, result);

			return (Guid)result.Rows[0][0];
		}

		public void RefreshUserSession(Guid token, DateTime expirationDate)
		{
			var command = new UpdateSessionCommand(token, expirationDate);

			using (var manager = SqlManager.Create(connectionString, false))
			{
				manager.ExecNonQuery(command);
			}
		}

		public void EndUserSession(Guid token)
		{
			var command = new DeleteSessionCommand(token);

			using (var manager = SqlManager.Create(connectionString, false))
			{
				manager.ExecNonQuery(command);
			}
		}


		public void UpdatePost(EditPostModel post, DateTime lastEdit, int lastEditUserID)
		{
			var command = new UpdatePostCommand(post, lastEdit, lastEditUserID);

			using (var manager = SqlManager.Create(connectionString, false))
			{
				manager.ExecNonQuery(command);
			}
		}

		public void ChangeLockState(int postID, bool locked)
		{

			var posts = GetPostForViewing(postID);
			// find this message
			var messageAndChildren = FindPostAndChildren(postID, posts.Thread.TopMessage);

			using (var manager = SqlManager.Create(connectionString, true))
			{
				var parentCommand = new UpdatePostLockStateCommand(messageAndChildren.PostDetails.PostID, locked);
				manager.ExecNonQuery(parentCommand);

				ChangeLockStateChildren(manager, messageAndChildren.ChildPosts, locked);

				manager.Commit();
			}
		}

		private void ChangeLockStateChildren(SqlManager manager, IList<MessageThread> children, bool locked)
		{
			foreach (var message in children)
			{
				var childCommand = new UpdatePostLockStateCommand(message.PostDetails.PostID, locked);
				manager.ExecNonQuery(childCommand);

				ChangeLockStateChildren(manager, message.ChildPosts, locked);
			}
		}

		private MessageThread FindPostAndChildren(int postId, MessageThread postToCheck)
		{

			if (postToCheck.PostDetails.PostID == postId)
			{
				return postToCheck;
			}

			MessageThread foundThread;

			if (TryFindPostInChildren(postId, postToCheck.ChildPosts, out foundThread))
			{
				return foundThread;
			}
			else
			{
				throw new InvalidOperationException("Could not find post in message thread");
			}

		}

		private bool TryFindPostInChildren(int postId, IList<MessageThread> children, out MessageThread foundThread)
		{
			foreach (var child in children)
			{
				if (child.PostDetails.PostID == postId)
				{
					foundThread = child;
					return true;
				}

				// wasn't this post, check children
				if (TryFindPostInChildren(postId, child.ChildPosts, out foundThread))
				{
					return true;
				}

			}

			foundThread = null;
			return false;
		}
		

		public DateTime GetSessionExpiration(Guid token)
		{
			var command = new GetSessionExpirationCommand(token);
			DataTable sessionTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				sessionTable = manager.GetDataTable(command);
			}

			switch (sessionTable.Rows.Count)
			{
				case 0:
					// expired or never existed
					return DateTime.MinValue;

				case 1:
					return (DateTime)sessionTable.Rows[0]["expirationdate"];

				default:
					// don't know wtf happened here, we have multiple non-expired sessions
					// kill them and return minvalue
					this.EndUserSession(token);
					return DateTime.MinValue;
			}
		}


		public EditProfileModel GetProfileForEditing(string username)
		{
			var command = new GetUserByNameCommand(username);

			DataTable userTable;

			using (var manager = SqlManager.Create(connectionString, false))
			{
				userTable = manager.GetDataTable(command);
			}

			ValidateRowCount(1, userTable);

			return editUserCreator.Create(userTable.Rows[0]);

		}

		public void UpdateProfile(EditProfileModel user)
		{
			var command = new UpdateProfileCommand(user);

			using (var manager = SqlManager.Create(connectionString, false))
			{
				manager.ExecNonQuery(command);
			}
		}

		public string GetUsername(int userID)
		{
			var command = new GetUsernameCommand(userID);

			DataTable userRow;
			using (var manager = SqlManager.Create(connectionString, false))
			{
				userRow = manager.GetDataTable(command);
			}

			ValidateRowCount(1, userRow);

			return userRow.Rows[0]["Username"] as string;
		}


		public int CreateReply(int userID, CreateReplyModel reply)
		{
			var command = new InsertReplyCommand(userID, reply);
			DataTable table;

			using (var manager = SqlManager.Create(connectionString, true))
			{
				try
				{
					table = manager.GetDataTable(command);
					manager.Commit();
				}
				catch
				{
					manager.Rollback();
					throw;
				}
			}

			ValidateRowCount(1, table);

			return int.Parse(string.Format("{0}", table.Rows[0]["id"]));
		}


		public IDictionary<int, ForumGroupAccess> GetGroupAccessToForum(int forumID)
		{
			var command = new GetGroupAccessToForumCommand(forumID);

			DataTable results;
			using (var manager = SqlManager.Create(connectionString))
			{
				results = manager.GetDataTable(command);
			}

			//
			var groups = new Dictionary<int, ForumGroupAccess>();
			foreach (var row in results.Rows.OfType<DataRow>())
			{
				var group = new ForumGroupAccess
				{
					GroupID = (int)row["groupid"],
					GroupName = string.Format("{0}", row["groupname"]),
				};

				var access = row["accesstype"] as int?;
				if (access.HasValue)
				{
					group.AccessType = (ForumAccessType)access.Value;
				}

				groups.Add(group.GroupID, group);
			}

			return groups;
		}

		public void UpdateForumAccessForForum(UpdateGroupPermissionsModel permissions)
		{
			var command = new UpdateGroupPermissionsForForumCommand(permissions);

			using (var manager = SqlManager.Create(connectionString))
			{
				manager.ExecNonQuery(command);
			}
		}


		public int CreateForum(CreateForumModel forum)
		{
			var command = new InsertForumCommand(forum);

			DataTable result;
			using (var manager = SqlManager.Create(connectionString))
			{
				result = manager.GetDataTable(command);
			}

			return int.Parse(string.Format("{0}", result.Rows[0]["id"]));

		}

		public void UpdateForum(EditForumModel forum)
		{
			var command = new UpdateForumCommand(forum);

			using (var manager = SqlManager.Create(connectionString))
			{
				manager.ExecNonQuery(command);
			}

		}

		public void DeleteForum(int forumID)
		{
			var command = new DeleteForumCommand(forumID);

			using (var manager = SqlManager.Create(connectionString))
			{
				manager.ExecNonQuery(command);
			}
		}

		public EditForumModel GetForumForEditing(int forumID)
		{
			var command = new GetForumByIDCommand(forumID);

			DataTable result;
			using (var manager = SqlManager.Create(connectionString))
			{
				result = manager.GetDataTable(command);
			}

			ValidateRowCount(1, result);

			var model = new EditForumModel
			{
				Description = string.Format("{0}", result.Rows[0]["description"]),
				ForumID = (int)result.Rows[0]["forumid"],
				IndexOrder = (int)result.Rows[0]["indexorder"],
				Title = string.Format("{0}", result.Rows[0]["title"]),
			};

			return model;
		}
		
		public void VerifyUser(string username)
		{
			var command = new VerifyUserCommand(username);

			using (var manager = SqlManager.Create(connectionString))
			{
				manager.ExecNonQuery(command);
			}
		}

		public void DeleteUser(string username)
		{
			var command = new DeleteUserByNameCommand(username);

			using (var manager = SqlManager.Create(connectionString))
			{
				manager.ExecNonQuery(command);
			}
		}

		public IEnumerable<OnlineUser> GetOnlineUsers(DateTime activeThreshold)
		{
			var command = new GetOnlineUsersCommand(activeThreshold);
			DataTable results;

			using (var manager = SqlManager.Create(connectionString))
			{
				results = manager.GetDataTable(command);
			}

			var users = new List<OnlineUser>();
			foreach (DataRow row in results.Rows)
			{
				var user = new OnlineUser();

				user.Name = (string)row["Username"];
				user.Group = (string)row["GroupName"];
				user.GroupColor = (string)row["GroupColor"];

				users.Add(user);
			}

			return users;
		}
	}
}
